using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Data;
using Newtonsoft.Json;

public enum SelectionFilterType { FILTER_TYPE_NUMERIC, FILTER_TYPE_STRING }
public enum SelectionFilterMode { FILTER_MODE_LT, FILTER_MODE_LEQ, FILTER_MODE_EQ, FILTER_MODE_GEQ, FILTER_MODE_GT, FILTER_MODE_NEQ }

public class SelectionFilter
{
    public SelectionFilterType targetType;
    public SelectionFilterMode targetMode;
    public string targetKey;
    public dynamic targetValue;
    public SelectionFilter(SelectionFilterType type, string key, SelectionFilterMode mode, dynamic value)
    {
        targetType = type;
        targetKey = key;
        targetMode = mode;
        targetValue = value;
    }

    public SelectionFilter(DataTable table, string desc)
    {
        string[] items = desc.Split(' ');
        if (items.Length == 3)
        {
            switch (items[1])
            {
                case "!=":
                    targetMode = SelectionFilterMode.FILTER_MODE_NEQ;
                    break;
                case "<":
                    targetMode = SelectionFilterMode.FILTER_MODE_LT;
                    break;
                case "<=":
                    targetMode = SelectionFilterMode.FILTER_MODE_LEQ;
                    break;
                case "==":
                    targetMode = SelectionFilterMode.FILTER_MODE_EQ;
                    break;
                case ">=":
                    targetMode = SelectionFilterMode.FILTER_MODE_GEQ;
                    break;
                case ">":
                    targetMode = SelectionFilterMode.FILTER_MODE_GT;
                    break;
                default:
                    throw new System.Exception(string.Format("invalid desc string: error compare symbol [{0}]", items[1]));
            }
            switch (table.Select(string.Format("name = '{0}'", items[0]))[0]["type"].ToString())
            {
                case "TEXT":
                    targetType = SelectionFilterType.FILTER_TYPE_STRING;
                    targetValue = items[2];
                    break;
                case "REAL":
                    targetType = SelectionFilterType.FILTER_TYPE_NUMERIC;
                    targetValue = float.Parse(items[2]);
                    break;
                case "INTEGER":
                    targetType = SelectionFilterType.FILTER_TYPE_NUMERIC;
                    targetValue = int.Parse(items[2]);
                    break;
                default:
                    throw new System.Exception(string.Format("invalid desc string: error colomn name [{0}]", items[0]));
            };
            targetKey = items[0];
        }
        else
        {
            throw new System.Exception("invalid desc string: wrong format");
        }
    }
}

public class DatabaseQuery
{
    private string mode;
    private string targetTable;
    private string[] targetAttrs;
    private List<string> cmdlines = new List<string>();

    public string GetMode()
    {
        return mode;
    }

    public string GetCMD()
    {
        return string.Join("\n", cmdlines) + ";";
    }

    public string[] GetTargetAttrs()
    {
        return targetAttrs;
    }

    public string GetTargetTableName()
    {
        return targetTable;
    }

    public DatabaseQuery CreateTable(DataTable desc)
    {
        if (mode == null)
        {
            mode = "create";
            targetTable = desc.TableName;

            string cmd = "CREATE TABLE " + desc.TableName + " ( ";
            foreach (DataRow row in desc.Rows)
            {
                cmd += row["name"] + " " + row["type"] + " " + row["attr"] + ", ";
            }
            cmd = cmd.Substring(0, cmd.Length - 2) + " )"; // remove last ','
            cmdlines.Add(cmd);
        }
        else
        {
            throw new System.Exception("cannot call CREATE while in mode " + mode);
        }
        return this;
    }

    public DatabaseQuery DropTable(string tableName)
    {
        if (mode == null)
        {
            mode = "drop";
            targetTable = tableName;
            string cmd = string.Format("DROP TABLE {0}", targetTable);
            cmdlines.Add(cmd);
        }
        else
        {
            throw new System.Exception("cannot call DROP while in mode " + mode);
        }
        return this;
    }

    public DatabaseQuery Update(DataTable tableDesc, string[] attrNames, dynamic[] values)
    {
        if (mode == null)
        {
            mode = "update";
            targetAttrs = attrNames;
            targetTable = tableDesc.TableName;

            cmdlines.Add("UPDATE " + tableDesc.TableName);
            string cmd = "SET ";
            for (int i = 0; i < attrNames.Length; i++)
            {
                cmd += attrNames[i] + " = ";
                DataRow desc = tableDesc.Select("name ='" + attrNames[i] + "'")[0];
                if (desc["type"].ToString() == "TEXT")
                {
                    cmd += "'" + values[i] + "'";
                }
                else
                {
                    cmd += values[i].ToString();
                }
                cmd += ", ";
            }
            cmd = cmd.Substring(0, cmd.Length - 2); // remove last ','
            cmdlines.Add(cmd);
        }
        else
        {
            throw new System.Exception("cannot call INSERT while in mode " + mode);
        }
        return this;
    }

    public DatabaseQuery InsertInto(DataTable tableDesc, string[] attrNames, dynamic[] values)
    {
        if (mode == null)
        {
            mode = "insert";
            targetAttrs = attrNames;
            targetTable = tableDesc.TableName;

            string cmd = "";
            cmd += "INSERT OR REPLACE INTO " + tableDesc.TableName;
            if (attrNames.Length > 0)
            {
                // set attr names
                cmd += " ( " + attrNames[0];
                foreach (var attrName in attrNames.Skip(1))
                {
                    cmd += ", " + attrName;
                }
                cmd += " )";
                cmdlines.Add(cmd);
                // set values
                string vals = "VALUES ( ";
                for (int i = 0; i < attrNames.Length; i++)
                {
                    DataRow desc = tableDesc.Select("name ='" + attrNames[i] + "'")[0];
                    if (desc["type"].ToString() == "TEXT")
                    {
                        vals += "'" + values[i] + "'";
                    }
                    else
                    {
                        vals += values[i].ToString();
                    }
                    vals += ", ";
                }
                vals = vals.Substring(0, vals.Length - 2) + " )"; // remove last ', '
                cmdlines.Add(vals);
            }
        }
        else
        {
            throw new System.Exception("cannot call INSERT while in mode " + mode);
        }
        return this;
    }

    public DatabaseQuery SelectFrom(string[] attrNames, string tableName)
    {
        if (mode == null)
        {
            mode = "select";
            targetAttrs = attrNames;
            targetTable = tableName;

            string cmd1 = "SELECT " + attrNames[0];
            foreach (var attrName in attrNames.Skip(1))
            {
                cmd1 += ", " + attrName;
            }
            cmdlines.Add(cmd1);
            string cmd2 = "FROM " + tableName;
            cmdlines.Add(cmd2);
        }
        else
        {
            throw new System.Exception("cannot call SELECT while in mode " + mode);
        }
        return this;
    }

    public DatabaseQuery Where(string desc)
    {
        if (mode == "select" || mode == "update")
        {
            mode += "_cond";
            string cmd = "";
            cmd += "WHERE " + GenCondExpr(new SelectionFilter(DataManager.dbDesc.Tables[targetTable], desc));
            cmdlines.Add(cmd);
        }
        else
        {
            throw new System.Exception("cannot call WHERE while in mode " + mode);
        }
        return this;
    }

    public DatabaseQuery And(string desc)
    {
        // check if is valid
        if (mode.EndsWith("cond"))
        {
            string cmd = "";
            cmd += "AND " + GenCondExpr(new SelectionFilter(DataManager.dbDesc.Tables[targetTable], desc));
            cmdlines.Add(cmd);
        }
        else
        {
            throw new System.Exception("cannot call AND while in mode " + mode);
        }
        return this;
    }

    public DatabaseQuery Or(string desc)
    {

        if (mode.EndsWith("cond"))
        {
            string cmd = "";
            cmd += "OR " + GenCondExpr(new SelectionFilter(DataManager.dbDesc.Tables[targetTable], desc));
            cmdlines.Add(cmd);
        }
        else
        {
            throw new System.Exception("cannot call OR while in mode " + mode);
        }
        return this;
    }

    private static Dictionary<SelectionFilterMode, string> selMode2QueryStr = new Dictionary<SelectionFilterMode, string>()
    {
        { SelectionFilterMode.FILTER_MODE_LT, "<" },
        { SelectionFilterMode.FILTER_MODE_LEQ, "<=" },
        { SelectionFilterMode.FILTER_MODE_EQ, "=" },
        { SelectionFilterMode.FILTER_MODE_GEQ, ">=" },
        { SelectionFilterMode.FILTER_MODE_GT, ">" },
        { SelectionFilterMode.FILTER_MODE_NEQ, "!=" }
    };
    private static string GenCondExpr(SelectionFilter cond)
    {
        string expr = cond.targetKey;
        expr += " " + selMode2QueryStr[cond.targetMode] + " ";
        if (cond.targetType == SelectionFilterType.FILTER_TYPE_STRING)
        {
            expr += "\'" + cond.targetValue + "\'";
        }
        else
        {
            expr += cond.targetValue;
        }
        return expr;
    }
}